﻿Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Collections
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DAAluno
  Inherits DAUsuario
  Private objBD As New BancoDeDados

  Public Function selecionaAluno(ByVal objBEAluno As BEAluno) As BEAluno
    Try
      Dim objDAUsuario As New DAUsuario
      objBEAluno = objDAUsuario.SelecionaUsuario(objBEAluno)
      objBEAluno.Cursos = Me.selecionarCursosAluno(objBEAluno)
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.AlunoDAO.selecionaAluno(): " & ex.Message)
    End Try
    Return objBEAluno
  End Function

  Public Function selecionaAlunos(ByVal objBEAluno As BEAluno) As IList(Of BEAluno)
    Dim objBEAlunos As List(Of BEAluno) = New List(Of BEAluno)
    Try
      objBD.duplicaAspasSimples(objBEAluno, GetType(BEAluno))
      objQuery._Select = "SELECT * FROM ext_usuario"
      If objBEAluno IsNot Nothing Then
        With objBEAluno
          If .Franquia IsNot Nothing AndAlso .Franquia.IdFranquia > 0 Then _
            objQuery.Where.AdicionarCondicaoWhere("id_franquia", .Franquia.IdFranquia, False)
          If .GrupoAcesso IsNot Nothing AndAlso .GrupoAcesso.IdGrupo > 0 Then _
            objQuery.Where.AdicionarCondicaoWhere("id_grupo_acesso", .GrupoAcesso.IdGrupo, False)
          objQuery.Where.AdicionarCondicaoWhere("nome", .Nome)
          objQuery.Where.AdicionarCondicaoWhere("cidade", .Cidade)
          objQuery.Where.AdicionarCondicaoWhere("usuario", .Usuario)
          objQuery.Where.AdicionarCondicaoWhere("senha", .Senha)
          objQuery.Where.AdicionarCondicaoWhere("cpf", .Cpf)
          If .Cursos IsNot Nothing AndAlso .Cursos.Count() > 0 Then
            Dim strCursosId As String = ""
            For Each objCurso In .Cursos
              If objCurso.Id > 0 Then strCursosId &= objCurso.Id & ","
            Next
            If strCursosId.Length() > 0 Then strCursosId = strCursosId.Substring(0, strCursosId.Length() - 1)
            If strCursosId.Length() > 0 Then _
              objQuery.Where.AdicionarCondicaoWhereSubSelect("usuario", _
                                                             "SELECT usuario FROM ext_rel_alunos_cursos " & _
                                                             "WHERE idCurso IN (" & strCursosId & ")")
          End If
        End With
      End If
      objBEAlunos = selecionaAlunos()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAluno.SelecionaAlunos(): " & ex.Message)
    End Try
    Return objBEAlunos
  End Function

  Private Function SelecionaAlunos() As List(Of BEAluno)
    Dim objBEAlunos As List(Of BEAluno) = New List(Of BEAluno)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            Dim objBEAluno As New BEAluno()
            Dim strGrupoAcesso As String
            Dim strFranquia As String
            With objBEAluno
              .Nome = objSQLDReader("nome").ToString()
              .Avatar = objSQLDReader("avatar")
              .Cpf = objSQLDReader("cpf").ToString()
              .Usuario = objSQLDReader("usuario").ToString()
              .Senha = objSQLDReader("senha").ToString()
              .Pais = objSQLDReader("pais")
              .Uf = objSQLDReader("uf")
              .Cidade = objSQLDReader("cidade")
              .Cep = objSQLDReader("cep")
              .Logradouro = objSQLDReader("logradouro")
              .Email = objSQLDReader("email")
              strGrupoAcesso = objSQLDReader("id_grupo_acesso").ToString()
              strFranquia = objSQLDReader("id_Franquia").ToString()

              .GrupoAcesso = New BEGrupoAcesso(Integer.Parse(IIf(strGrupoAcesso.Length > 0, strGrupoAcesso, "0")))
              .Franquia = New BEFranquia(Integer.Parse(IIf(strFranquia.Length > 0, strFranquia, "0")))
            End With
            objBEAlunos.Add(objBEAluno)
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAAluno.SelecionaAlunos(): " & ex.Message)
    End Try
    Return objBEAlunos
  End Function

  Public Function ContarRespostasCertas(ByVal objBEAluno As BEAluno) As Integer
    Dim intRespostasCertas As Integer = 0
    'objQuery._Select = "SELECT count(*) FROM ext_notas_aluno nc " & _
    '                   "INNER JOIN ext_questoes q ON nc.questaoid=q.id " & _
    '                   "WHERE alunoid=" & objBEAluno.Id & _
    '                   "  AND provaid=" & objBEAluno.CursosAssociadas(0).Prova.Id & _
    '                   " AND (q.respostaDissertativa=1 OR " & _
    '                         "nc.alternativaid=(SELECT aq.id FROM ext_alternativas_questao aq " & _
    '                                           "WHERE nc.questaoid=aq.questaoid AND aq.alternativacerta=1))"
    'Try
    '  Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
    '    Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
    '      While objSQLDReader.Read()
    '        intRespostasCertas = objSQLDReader(0)
    '      End While
    '    End Using
    '  End Using
    'Catch ex As ApplicationException
    '  Throw New ApplicationException("extranet.DA.AlunoDAO.ContarRespostasCertas(): " & ex.Message)
    'End Try
    Return intRespostasCertas
  End Function

  Public Function SelecionarCursosAluno(ByRef objBEAluno As BEAluno) As List(Of BECurso)
    Dim objDACurso As New DACurso
    Dim objBECurso As New BECurso()
    objBECurso.Alunos = New List(Of BEAluno)
    objBECurso.Alunos.Add(objBEAluno)
    objBEAluno.Cursos = objDACurso.Seleciona(objBECurso)
    Return objBEAluno.Cursos
  End Function

End Class
